package com.my.db.dataCleaning;

import lombok.AllArgsConstructor;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
//数据清洗示例---仅做参考
public class DataCleaningExampleSimpleExample{

    public static void main(String[] args) {
        try (Connection conn = DatabaseUtil.getConnection()) {
            // 从源表中检索数据
            String selectQuery = "SELECT id, name, description FROM source_table";
            try (Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(selectQuery)) {

                // 准备插入到目标表的 SQL 语句
                String insertQuery = "INSERT INTO target_table (id, name, description) VALUES (?,?,?)";
                try (PreparedStatement pstmt = conn.prepareStatement(insertQuery)) {
                    while (rs.next()) {
                        int id = rs.getInt("id");
                        // 清洗 name 字段，去除首尾空白字符
                        String name = rs.getString("name").trim();
                        // 清洗 description 字段，去除首尾空白字符
                        String description = rs.getString("description").trim();

                        // 设置插入语句的参数
                        pstmt.setInt(1, id);
                        pstmt.setString(2, name);
                        pstmt.setString(3, description);

                        // 执行插入操作
                        pstmt.executeUpdate();
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
//从包含 part_id 字段的表中读取数据，再根据 part_id 去另一张表中查询对应的 code 值。可以使用 PreparedStatement 防止 SQL 注入，示例代码如下：
class DataCleaning {
    public static Map<String, String> getCodeMap() {
        Map<String, String> codeMap = new HashMap<>();
        try (Connection conn = DatabaseUtil.getConnection();
             // 假设表名为table1（含part_id）和table2（含code和id，id是主键）
             PreparedStatement ps1 = conn.prepareStatement("SELECT part_id FROM table1");
             ResultSet rs1 = ps1.executeQuery()) {
            while (rs1.next()) {
                String partId = rs1.getString("part_id");
                try (PreparedStatement ps2 = conn.prepareStatement("SELECT code FROM table2 WHERE id =?")) {
                    ps2.setString(1, partId);
                    try (ResultSet rs2 = ps2.executeQuery()) {
                        if (rs2.next()) {
                            String code = rs2.getString("code");
                            codeMap.put(partId, code);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return codeMap;
    }
}
//如果获取到 code 值后，还需要对原表数据进行更新等操作，可继续编写代码。例如，将获取到的 code 值更新到原表的一个新字段中（假设原表新增了 code 字段 ）：
class DataUpdate {
    public static void updateTableWithCode() {
        Map<String, String> codeMap = DataCleaning.getCodeMap();
        try (Connection conn = DatabaseUtil.getConnection()) {
            for (Map.Entry<String, String> entry : codeMap.entrySet()) {
                String partId = entry.getKey();
                String code = entry.getValue();
                String updateSql = "UPDATE table1 SET code =? WHERE part_id =?";
                try (PreparedStatement ps = conn.prepareStatement(updateSql)) {
                    ps.setString(1, code);
                    ps.setString(2, partId);
                    ps.executeUpdate();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
//批量操作：对于更新等操作，可使用 JDBC 的批量更新功能，减少数据库交互次数。如将上述更新代码修改为批量更新
class DataUpdateBatch {
    public static void updateTableWithCode() {
        Map<String, String> codeMap = DataCleaning.getCodeMap();
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement("UPDATE table1 SET code =? WHERE part_id =?")) {
            conn.setAutoCommit(false);
            for (Map.Entry<String, String> entry : codeMap.entrySet()) {
                String partId = entry.getKey();
                String code = entry.getValue();
                ps.setString(1, code);
                ps.setString(2, partId);
                ps.addBatch();
            }
            ps.executeBatch();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
class DatabaseUtil {
    private static final String URL = "jdbc:mysql://localhost:3306/your_database?useSSL=false&serverTimezone=UTC";
    private static final String USER = "your_username";
    private static final String PASSWORD = "your_password";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
}
//在从数据库中检索大量数据时，采用分页查询的方式，避免一次性加载过多数据到内存中。
class PaginationExample {
    private static final int PAGE_SIZE = 1000;

    public static void main(String[] args) {
        int offset = 0;
        try (Connection conn = DatabaseUtil.getConnection()) {
            while (true) {
                String selectQuery = "SELECT id, name, description FROM source_table LIMIT? OFFSET?";
                try (PreparedStatement pstmt = conn.prepareStatement(selectQuery)) {
                    pstmt.setInt(1, PAGE_SIZE);
                    pstmt.setInt(2, offset);
                    try (ResultSet rs = pstmt.executeQuery()) {
                        boolean hasData = false;
                        while (rs.next()) {
                            hasData = true;
                            int id = rs.getInt("id");
                            String name = rs.getString("name");
                            String description = rs.getString("description");
                            // 处理数据
                        }
                        if (!hasData) {
                            break;
                        }
                    }
                }
                offset += PAGE_SIZE;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
//利用 Java 的多线程或并行流来并行处理数据。以下是使用并行流处理数据的示例：
class ParallelDataProcessing {
    public static void main(String[] args) {
        List<Data> dataList = new ArrayList<>();
        // 模拟大量数据
        for (int i = 0; i < 10000; i++) {
            dataList.add(new Data(i, "Name" + i, "Description" + i));
        }

        List<Data> cleanedData = dataList.parallelStream()
                .map(data -> {
                    data.setName(data.getName().trim());
                    data.setDescription(data.getDescription().trim());
                    return data;
                })
                .collect(Collectors.toList());
    }
}
//CREATE INDEX idx_name ON target_table (name);
//确保在查询涉及的字段上创建合适的索引，能够加快查询速度。例如，若经常根据 name 字段查询数据，可在 name 字段上创建索引：
@lombok.Data
@AllArgsConstructor
class Data {
    private int id;
    private String name;
    private String description;

}
